# -*- coding: utf-8 -*-
from __future__ import unicode_literals
import pandas as pd
"""
demo10_pivot.py 透视表
"""
left = pd.DataFrame({
    'student_id': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    'student_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung', 'Billy', 'Brian', 'Bran', 'Bryce', 'Betty'],
    'class_id': [1, 1, 1, 2, 2, 2, 3, 3, 3, 4],
    'gender': ['M', 'F', 'M', 'M', 'F', 'M', 'F', 'F', 'F', 'F'],
    'score': [92, 33, 46, 52, 34, 52, 34, 56, 51, 15],
    'age': [23, 23, 24, 23, 24, 24, 23, 24, 25, 25]})
right = pd.DataFrame(
    {'class_id': [1, 2, 3, 5],
     'class_name': ['ClassA', 'ClassB', 'ClassC', 'ClassE']})
# 合并两个DataFrame
rs = pd.merge(left, right)
print(rs)

# 透视表，以classid与gender字段进行分组
r = rs.pivot_table(index=['class_id', 'gender'])
print('-----\n', r)
# 透视表，以classid与gender字段进行分组
# 分组后输出每组数据score字段的均值
r = rs.pivot_table(
    index=['class_id', 'gender', 'age'],
    values=['score'])
print('-----\n', r)
r = rs.pivot_table(
    index=['class_id', 'gender'],
    columns=['age'],
    values=['score'])
print('-----\n', r)
